import 'dart:convert';
import 'dart:io';

import 'package:m_todo/domain/entity/todo_entity.dart';
import 'package:m_todo/domain/store/base_todo_store.dart';
import 'package:m_todo/store/file_todo_util.dart';
import 'package:path/path.dart';
import 'package:sqlite3/sqlite3.dart';

import 'config_file_util.dart';

class DBTodoStore extends BaseTodoStore {
  final ConfigFileUtil _configFileUtil = ConfigFileUtil();
  final String name;

  DBTodoStore(this.name);

  // 是否支持数据库
  bool support() {
    try {
      return sqlite3.version.libVersion.isNotEmpty;
    } catch (e) {
      return false;
    }
  }

  // 是否初始化
  Future<bool> isEnableDB() async {
    var config = await _configFileUtil.getConfig();
    return config.isEnableDB;
  }

  // 初始化
  Future<bool> init() async {
    var db = _open();
    try {
      // 创建表
      db.execute('''
    CREATE TABLE todo (
      id INTEGER NOT NULL PRIMARY KEY,
      content TEXT NOT NULL,
      name TEXT NOT NULL,
      index_ INT NOT NULL,
      state INT NOT NULL
    );
  ''');
    } catch (e) {
      return Future.value(false);
    } finally {
      db.dispose();
    }

    // 初始化成功写入配置文件
    await _configFileUtil.updateConfig((config) => config.isEnableDB = true);
    return Future.value(true);
  }

  Database _open() {
    return sqlite3.open('./data/todo.db');
  }

  @override
  Future<int> add(TodoEntity todoEntity) async {
    var db = _open();
    try {
      // 查询数量
      final int count = await queryCountByName();
      final int index = count + 1;

      // 创建表
      db.execute('''
  INSERT INTO todo (content, name, index_, state) VALUES ('${todoEntity.content}', '$name',$index ,${todoEntity.state});
  ''');

      return index;
    } catch (e) {
      return Future.error(e);
    } finally {
      db.dispose();
    }
  }

  @override
  Future<TodoEntity?> query(int index) async {
    var db = _open();
    try {
      var result = db.select("""
    SELECT * FROM todo
    WHERE name = '$name' AND index_ = $index;
    """);

      var row = result.rows[0];
      // var id = row[0];
      var content = row[1] ?? '';
      // var name = row[2];
      var index_ = row[3];
      var state = row[4];

      return TodoEntity(content: content.toString(), index: int.parse(index_.toString()), state: int.parse(state.toString()));
    } catch (e) {
      return null;
    } finally {
      db.dispose();
    }
  }

  @override
  Future<List<TodoEntity>> queryAll() {
    var db = _open();
    List<TodoEntity> list = [];

    try {
      var result = db.select("""
    SELECT * FROM todo
    WHERE name = '$name';
    """);

      result.rows.map((row) {
        // var id = row[0];
        var content = row[1] ?? '';
        // var name = row[2];
        var index_ = row[3];
        var state = row[4];
        var item = TodoEntity(content: content.toString(), index: int.parse(index_.toString()), state: int.parse(state.toString()));
        list.add(item);
      }).toList();
    } catch (e) {} finally {
      db.dispose();
    }
    return Future.value(list);
  }

  @override
  Future<List<TodoEntity>> queryUnDoneList() {
    var db = _open();
    List<TodoEntity> list = [];

    try {
      var result = db.select("""
    SELECT * FROM todo
    WHERE name = '$name' AND state = ${TodoStateConst.unDone};
    """);

      result.rows.map((row) {
        // var id = row[0];
        var content = row[1] ?? '';
        // var name = row[2];
        var index_ = row[3];
        var state = row[4];
        var item = TodoEntity(content: content.toString(), index: int.parse(index_.toString()), state: int.parse(state.toString()));
        list.add(item);
      }).toList();
    } catch (e) {} finally {
      db.dispose();
    }
    return Future.value(list);
  }

  @override
  Future<void> update(TodoEntity todoEntity) async {
    var db = _open();
    try {
      db.execute('''
UPDATE todo
SET content='${todoEntity.content}',state=${todoEntity.state}
WHERE name='${name}' AND index_=${todoEntity.index};
  ''');
    } catch (e) {
      return Future.error(e);
    } finally {
      db.dispose();
    }
  }

  @override
  Future<String> export(String targetPath) async {
    try {
      // 创建目录
      var dir = Directory(targetPath);
      if (!await dir.exists()) {
        try {
          await dir.create(recursive: true);
        } catch (e) {
          return Future.error('文件路径无效：$targetPath');
        }
      }
      // 查询该用户所有的todo
      var todoList = await queryAll();

      // 时间戳作为流水号
      var no = DateTime.now().microsecondsSinceEpoch;
      var fileName = 'todo$no.json';
      var path = join(targetPath, fileName);

      // 写入文件
      var targetFile = File(path);
      await FileTodoUtil.saveStrToFile(targetFile, jsonEncode(todoList));

      // 返回完成路径
      return join(targetPath, fileName);
    } catch (e) {
      return Future.error(e);
    }
  }

  @override
  Future<int> import(String targetFilePath) async {
    try {
      var targetFile = File(targetFilePath);
      if (!await targetFile.exists()) {
        return Future.error('无效文件');
      }

      // 加载todo文件
      var newTodoList = await FileTodoUtil.loadTodoList(targetFile).then((value) => value.map<TodoEntity>((e) => TodoEntity.fromJson(e)).toList());
      // 当前todo数量
      final int count = await queryCountByName();

      if (newTodoList.isNotEmpty) {
        for (var i = 0; i < newTodoList.length; i++) {
          var item = newTodoList[i];
          // 修正index
          item.index = count + 1;

          // 写入数据库
          await add(item);
        }
      }

      return newTodoList.length;
    } catch (e) {
      return Future.error(e);
    }
  }

  /// 查询某个用户todo数量
  Future<int> queryCountByName() async {
    var db = _open();
    try {
      var result = db.select("""
    SELECT COUNT(*) AS "counts"
    FROM todo
    WHERE name = '$name';
    """);
      return int.tryParse(result.rows[0][0].toString()) ?? 0;
    } catch (e) {
      return 0;
    } finally {
      db.dispose();
    }
  }
}

main() async {
  var dbStore = DBTodoStore('lee');
  // print(dbStore.support());
  // if (!await dbStore.isEnableDB()) {
  //   await dbStore.init();
  // }
  // print(await dbStore.add(TodoEntity(content: "dddddwsediygh", state: TodoStateConst.unDone)));
  //
  // print(await dbStore.queryCountByName('lee'));
  //
  // print(await dbStore.query(1));
  //
  // print(await dbStore.queryAll());
  //
  // print(await dbStore.queryUnDoneList());
  //
  // await dbStore.update(TodoEntity(content: '多得多111', state: TodoStateConst.unDone, index: 10));

  // print(await dbStore.export('/Users/lizhengyang/Desktop/'));

  await dbStore.import('/Users/lizhengyang/Desktop/todo1643441748901295.json');
}
